MySQL 什么时候需要手动加锁

引言

首先问自己一个问题，下面这 6 句 SQL 的区别

select * from table where id = ?
select * from table where id < ?
select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update

这六句 SQL 在不同的事务隔离级别下，是否加锁，加的是共享锁还是排他锁，是否存在间隙锁

要回答这个问题，先问自己三个问题

• 当前事务隔离级别是什么
• id 列是否存在索引
• 如果存在索引是聚簇索引还是非聚簇索引呢？

首先回顾一下数据库的索引：

• innodb 一定存在聚簇索引，默认以主键作为聚簇索引
• 有几个索引，就有几棵 B+ 树（不考虑 hash 索引的情形）
• 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引，指向聚簇索引 B+ 树。

下面开始学习（前面几节是基础，最好看下才能理解后面的加锁）：

回顾一下锁类型

共享锁（S锁）：假设事务 T1 对数据 A 加上共享锁，那么事务 T2 可以读数据 A，不能修改数据 A。 排他锁（X锁）：假设事务 T1 对数据 A 加上共享锁，那么事务 T2 不能读数据 A，不能修改数据 A。

我们通过 update、delete 等语句加上的锁都是行级别的锁。只有 LOCK TABLE … READ 和 LOCK TABLE … WRITE 才能申请表级别的锁。

意向共享锁（IS锁）：一个事务在获取（任何一行/或者全表）S 锁之前，一定会先在所在的表上加 IS 锁。 意向排他锁（IX锁）：一个事务在获取（任何一行/或者全表）X 锁之前，一定会先在所在的表上加 IX 锁。

意向锁的作用可以参考之前那篇笔记~

加锁算法

Record Locks：简单翻译为行锁吧。注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！

Gap Locks：简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在 Read Committed 隔离级别下，不会使用间隙锁。这里对官网补充一下，隔离级别比 Read Committed 低的情况下，也不会使用间隙锁，如隔离级别为 Read Uncommited 时，也不存在间隙锁。当隔离级别为 Repeatable Read 和 Serializable 时，就会存在间隙锁。

Next-Key Locks：这个理解为 Record Lock + 索引前面的 Gap Lock。（这个 Next-Key Locks 会自动锁切换）记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13和20。那么，间隙锁的范围如下

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

快照读和当前读

在 mysql 中 select 分为快照读和当前读，执行下面的语句

select * from table where id = ?;

执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为 Serializable 中不成立，后面我会补充。）

那么，执行

select * from table where id = ? lock in share mode;

会对读取记录加 S 锁（共享锁），执行

select * from table where id = ? for update

会对读取记录加 X 锁（排他锁），那么

X 锁是表锁还是行锁呢？

针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示：

Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！ Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据! Repeatable Read (RR)：可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免! 另外就是记住从该级别才开始加入间隙锁 Serializable：串行化，该级别下读写串行化，且所有的 select 语句后都自动加上 lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

这里的可重复读出现幻读的参考：【十四】MySQL Innodb RR隔离级别下到底是不是解决了幻读

那么关于是表锁还是行锁，大家可以看到网上最流传的一个说法是这样的：

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

这句话大家可以搜一下，都是你抄我的，我抄你的。那么，这句话本身有两处错误！ 错误一： 并不是用表锁来实现锁表的操作，而是利用了 Next-Key Locks，也可以理解为是用了行锁 + 间隙锁来实现锁表的操作!

为了便于说明，我来个例子，假设有表数据如下，pId 为主键索引

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
7	ccc	200

执行语句（name 列无索引）

select * from table where name = `aaa` for update

那么此时在 pId=1,2,7 这三条记录上存在行锁（把行锁住了）。另外，在 (-∞,1)(1,2)(2,7)(7,+∞) 上存在间隙锁（把间隙锁住了）。因此，给人一种整个表锁住的错觉！

错误二： 所有文章都不提隔离级别！

注意我上面说的，之所以能够锁表，是通过行锁 + 间隙锁来实现的。那么，RU 和 RC 都不存在间隙锁，这种说法在 RU 和 RC 中还能成立么？

因此，该说法只在 RR 和 Serializable 中是成立的。如果隔离级别为 RU 和 RC，无论条件列上是否有索引，都不会锁表，只锁行！

意向锁存在的目的？

上面说了 X 锁不是表级锁，那这里说一下意向锁（表锁）存在的目的，以免搞混。

假设事务 T1，用 X 锁来锁住了表上的几条记录，那么此时表上存在 IX 锁，即意向排他锁。那么此时事务 T2 要进行 LOCK TABLE … WRITE 的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

开始提到的 6 句 SQL

下面来对开始的问题作出解答，假设有表如下，pId 为主键索引

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
3	bbb	300
7	ccc	200

下面来探讨各个隔离级别使用各种锁的情况

RC/RU + 条件列非索引

1、不加任何锁，是快照读。

select * from table where num = 200

2、不加任何锁，是快照读。

select * from table where num > 200

3、当 num = 200，有两条记录。这两条记录对应的 pId=2,7，因此在 pId=2,7 的聚簇索引上加行级S锁，采用当前读。

select * from table where num = 200 lock in share mode

4、当 num > 200，有一条记录。这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加上行级S锁，采用当前读。

select * from table where num > 200 lock in share mode

5、当 num = 200，有两条记录。这两条记录对应的 pId=2,7，因此在 pId=2,7 的聚簇索引上加行级X锁，采用当前读。

select * from table where num = 200 for update

6、当 num > 200，有一条记录。这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加上行级X锁，采用当前读。

select * from table where num > 200 for update

RC/RU + 条件列是聚簇索引

1、不加任何锁，是快照读。

select * from table where pId = 2

2、不加任何锁，是快照读。

select * from table where pId > 2

3、在 pId=2 的聚簇索引上，加S锁，为当前读。

select * from table where pId = 2 lock in share mode

4、在 pId= 3,7 的聚簇索引上，加S锁，为当前读。

select * from table where pId > 2 lock in share mode

5、在 pId=2 的聚簇索引上，加X锁，为当前读。

select * from table where pId = 2 for update

6、在 pId=3,7 的聚簇索引上，加X锁，为当前读。

select * from table where pId > 2 for update

比较上下两种，为什么条件列加不加索引，加锁情况是一样的？

其实是不一样的。在 RC/RU 隔离级别中，MySQL Server 做了优化。

在条件列没有索引的情况下，是通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server 层 会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果是一样的。但是 RC/RU + 条件列为非索引的比本例多了一个释放不符合条件的锁的过程！（即：没有索引的话，会先全表加锁，再筛选出不需要加锁的行，释放锁）

RC/RU + 条件列是非聚簇索引

我们在 num 列上建上非唯一索引。

此时有一棵聚簇索引（主键索引，pId）形成的 B+ 索引树，其叶子节点为硬盘上的真实数据。 以及另一棵非聚簇索引（非唯一索引，num）形成的 B+ 索引树，其叶子节点依然为索引节点，保存了 num 列的字段值，和对应的聚簇索引。

接下来分析开始

1、不加任何锁，是快照读。

select * from table where num = 200

2、不加任何锁，是快照读。

select * from table where num > 200

3、当 num = 200，由于 num 列上有索引，因此先在 num = 200 的两条索引记录上加行级S锁。接着，去聚簇索引树上查询，这两条记录对应的 pId=2,7，因此在 pId=2,7 的聚簇索引上加行级S锁，采用当前读。

select * from table where num = 200 lock in share mode

4、当 num > 200，由于 num 列上有索引，因此先在符合条件的 num = 300 的一条索引记录上加行级S锁。接着，去聚簇索引树上查询，这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加行级S锁，采用当前读。

select * from table where num > 200 lock in share mode

5、当 num = 200，由于 num 列上有索引，因此先在 num = 200 的两条索引记录上加行级X锁。接着，去聚簇索引树上查询，这两条记录对应的 pId=2,7，因此在 pId=2,7 的聚簇索引上加行级X锁，采用当前读。

select * from table where num = 200 for update

6、当 num > 200，由于 num 列上有索引，因此先在符合条件的 num = 300 的一条索引记录上加行级X锁。接着，去聚簇索引树上查询，这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加行级X锁，采用当前读。

select * from table where num > 200 for update

RR/Serializable + 条件列非索引

RR 级别需要多考虑的就是 gap lock，他的加锁特征在于，无论你怎么查都是锁全表。如下所示

1、在 RR 级别下，不加任何锁，是快照读。而在 Serializable 级别下，在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

select * from table where num = 200

2、在 RR 级别下，不加任何锁，是快照读。而在 Serializable 级别下，在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

select * from table where num > 200

3、在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 S 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock（注意 RR 也一样）

select * from table where num = 200 lock in share mode

4、在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

select * from table where num > 200 lock in share mode

5、在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

select * from table where num = 200 for update

6、在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

select * from table where num > 200 for update

RR/Serializable + 条件列是聚簇索引

pId 是主键列，因此 pId 用的就是聚簇索引。该情况的加锁特征在于，如果 where 后的条件为精确查询（= 的情况），那么只存在 record lock。如果 where 后的条件为范围查询（> 或 < 的情况），那么存在的是 record lock + gap lock。

1、在 RR 级别下，不加任何锁，是快照读。在 Serializable 级别下，是当前读，在 pId=2 的聚簇索引上加S锁，不存在 gap lock。

select * from table where pId = 2

2、在 RR 级别下，不加任何锁，是快照读。在 Serializable 级别下，是当前读，在 pId=3,7 的聚簇索引上加S锁。在 (2,3)(3,7)(7,+∞) 加上 gap lock

select * from table where pId > 2

3、是当前读（注意 RR 加上也一样变当前读了），是当前读，在 pId=2 的聚簇索引上加S锁，不存在 gap lock。

select * from table where pId > 2 lock in share mode

4、是当前读，是当前读，在 pId=3,7 的聚簇索引上加 S 锁。在 (2,3)(3,7)(7,+∞) 加上 gap lock

select * from table where pId > 2 lock in share mode

5、是当前读，是当前读，在 pId=2 的聚簇索引上加X锁。

select * from table where pId = 2 for update

6、在 pId=3,7 的聚簇索引上加X锁。在 (2,3)(3,7)(7,+∞) 加上 gap lock

select * from table where pId > 2 for update

下面介绍两种特殊情况

7、注意了，pId=6 是不存在的列，这种情况会在 (3,7) 上加 gap lock。

select * from table where pId = 6 [lock in share mode|for update]

8、注意了，pId > 18，查询结果是空的。在这种情况下，是在 (7,+∞) 上加 gap lock。

select * from table where pId > 18 [lock in share mode|for update]

RR/Serializable + 条件列是非聚簇索引

这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。

先说一下，唯一索引的情况。如果是唯一索引，情况和 RR/Serializable + 条件列是聚簇索引类似，唯一有区别的是：这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上！大家可以自行推敲!

下面说一下，非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在 record lock，还存在 gap lock。而通过唯一索引进行精确查询后，只存在 record lock，不存在 gap lock。老规矩在 num 列建立非唯一索引

1、在 RR 级别下，不加任何锁，是快照读。在 Serializable 级别下，是当前读，在 pId=2,7 的聚簇索引上加S锁，在 num=200 的非聚集索引上加S锁，在 (100,200)(200,300) 加上 gap lock。

select * from table where num = 200

2、在 RR 级别下，不加任何锁，是快照读。在 Serializable 级别下，是当前读，在 pId=3 的聚簇索引上加 S 锁，在 num=300 的非聚集索引上加S锁。在 (200,300)(300,+∞) 加上 gap lock

select * from table where num > 200

3、是当前读，在 pId=2,7 的聚簇索引上加S锁，在 num=200 的非聚集索引上加S锁，在 (100,200)(200,300) 加上gap lock。

select * from table where num = 200 lock in share mode

4、是当前读，在 pId=3 的聚簇索引上加S锁，在 num=300 的非聚集索引上加S锁。在 (200,300)(300,+∞) 加上 gap lock。

select * from table where num > 200 lock in share mode

5、是当前读，在 pId=2,7 的聚簇索引上加S锁，在 num=200 的非聚集索引上加X锁，在 (100,200)(200,300) 加上 gap lock。

select * from table where num = 200 for update

6、是当前读，在 pId=3 的聚簇索引上加S锁，在 num=300 的非聚集索引上加X锁。在 (200,300)(300,+∞) 加上 gap lock

select * from table where num > 200 for update

7、注意了，num=250 是不存在的列，这种情况会在 (200,300) 上加 gap lock。

select * from table where num = 250 [lock in share mode|for update]

8、注意了，pId>400，查询结果是空的。在这种情况下，是在 (400,+∞) 上加 gap lock。

select * from table where num > 400 [lock in share mode|for update]

RR 是否解决了幻读？

https://www.cnblogs.com/rjzheng/p/9955395.html

RR 基本下依旧存在幻读的情况，举个例子

事务A	事务B
select
	insert
update
select

例如这个事务 A，虽然它读的是 MVCC 的快照，但是当事务 B 提交插入，而事务 A 又执行了 Update 时（update 需要取得最新的数据，所以之后快照也会更新）就会幻读

如何解决呢？使用 next-key locks

我们在该隔离级别下执行语句

select *  from tx_tb where pId >= 1;

是快照读，是不加任何锁的，根本不能解决幻读问题，除非你用

-- RR 加上 lock in share mode; 也变当前读了
select *  from tx_tb where pId >= 1 lock in share mode;

这样，你就用上了next-key locks，解决了幻读问题！

References

• 转载自 【原创】惊！史上最全的select加锁分析(Mysql)（这里对原文做微小的补充删减）